package com.seaboxdata.jt809.db;

import com.seaboxdata.jt809.common.util.ByteArrayUtil;
import com.seaboxdata.jt809.packet.JT809Packet0x1202;
import com.seaboxdata.jt809.packet.JT809Packet0x1402;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.math.BigDecimal;
import java.sql.*;

import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.ZonedDateTime;
import java.time.format.DateTimeFormatter;
import java.util.List;
import java.util.UUID;

/**
 * @Author: dfjx
 * @Date: 2020/7/8 16:47
 * @Version 1.0
 * @Describe:
 */
public class JT809Dao {
    private static Logger log = LoggerFactory.getLogger(JT809Dao.class);

    public static int insert0x1202(JT809Packet0x1202 packet) {
        QueryRunner runner = new QueryRunner();
        Connection conn = DataSourceConnectionFactory.getDbConnection();
        String sql = "insert into car_gps(id,vehicleno,lon,lat,sendtime,cjsj,vec1,vec2,vec3,direction,altitude,alarm,state)values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
        int insert = 0;
        try {
            System.out.println("调用数据插入。。。。");
            insert = runner.update(conn, sql, UUID.randomUUID().toString().replace("-", ""), packet.getVehicleNo().replaceAll("\u0000", ""), packet.getLon(), packet.getLat(), LocalDateTime.of(packet.getDate(), packet.getTime()), LocalDateTime.now(), packet.getVec1(), packet.getVec2(), packet.getVec3(), packet.getDirection(), packet.getAltitude(), packet.getAlarm(), packet.getState());
        } catch (SQLException e) {
            log.info("数据存储错误：{}", e.getMessage());
        } finally {
            DbUtils.closeQuietly(conn);
        }
        return insert;
    }
    /**
     * @Author DFJX
     * @Description 批量插入车辆gps数据
     * @Date 2020/7/21 12:24
     * @Param [list]
     * @return void
     **/
    public static void batchAdd0x1202(List<JT809Packet0x1202> list) {
        Connection conn = DataSourceConnectionFactory.getDbConnection();
        String sql = "insert into dpjtys_yxzsxt.car_gps(id,vehicleno,lon,lat,sendtime,cjsj,vec1,vec2,vec3,direction,altitude,alarm,state)values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
        //int insert = 0;
        PreparedStatement preparedStatement = null;
        try {
            System.out.println("调用数据批量插入。。。。");
            conn.setAutoCommit(false);
            preparedStatement = conn.prepareStatement(sql);
            for (JT809Packet0x1202 packet : list) {
                preparedStatement.setString(1, UUID.randomUUID().toString().replace("-", ""));
                preparedStatement.setString(2, packet.getVehicleNo());
                //车辆经度
                preparedStatement.setBigDecimal(3, BigDecimal.valueOf(Double.parseDouble(packet.getLon()+"")/1000000.0).setScale(6));
                //车辆维度
                preparedStatement.setBigDecimal(4,BigDecimal.valueOf(Double.parseDouble(packet.getLat()+"")/1000000.0).setScale(6));
                ZoneId zoneId = ZoneId.systemDefault();
                ZonedDateTime zdt = LocalDateTime.of(packet.getDate(), packet.getTime()).atZone(zoneId);
                preparedStatement.setTimestamp(5,new Timestamp(java.util.Date.from(zdt.toInstant()).getTime()));
                preparedStatement.setTimestamp(6, new Timestamp(packet.getCjsj().getTime()));
                preparedStatement.setInt(7,packet.getVec1());
                preparedStatement.setInt(8,packet.getVec2());
                preparedStatement.setInt(9,packet.getVec3());
                preparedStatement.setInt(10,packet.getDirection());
                preparedStatement.setInt(11,packet.getAltitude());
                String alam=ByteArrayUtil.bytes2bitStr(ByteArrayUtil.int2bytes(packet.getAlarm()));
                int i=0;
                //车辆报警状态拼接
                String alarms="";
                for(char a:alam.toCharArray()){
                    i++;
                    if(a=='1'){
                        if(alarms.trim().length()==0){
                            alarms+=i;
                        }else{
                            alarms+=","+i;
                        }
                    }
                }
                if(alarms.trim().length()==0){
                    alarms="0";
                }
                preparedStatement.setString(12,alarms);
                String state=ByteArrayUtil.bytes2bitStr(ByteArrayUtil.int2bytes(packet.getState()));
                int j=0;
                //车辆状态拼接
                String states="";
                for(char a:state.toCharArray()){
                    if(j==0){
                        states=j+":"+a;
                    }else{
                        states+=","+j+":"+a;
                    }
                    j++;

                }
                if(states.trim().length()==0){
                    states="0";
                }
                preparedStatement.setString(13,states);
                preparedStatement.addBatch();
            }
            preparedStatement.executeBatch();
            conn.commit();
            //insert = runner.update(conn, sql, UUID.randomUUID().toString().replace("-", ""), packet.getVehicleNo().replaceAll("\u0000", ""), packet.getLon(), packet.getLat(), LocalDateTime.of(packet.getDate(), packet.getTime()), LocalDateTime.now(), packet.getVec1(), packet.getVec2(), packet.getVec3(), packet.getDirection(), packet.getAltitude(), packet.getAlarm(), packet.getState());
        } catch (Exception e) {
            e.printStackTrace();
            log.info("数据存储错误：{}", e.getMessage());
            try {
                if(conn!=null&&!conn.isClosed()){
                    conn.rollback();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            DbUtils.closeQuietly(conn);
        }
    }

    public static int insert0x1402(JT809Packet0x1402 packet) {
        QueryRunner runner = new QueryRunner();
        Connection conn = DataSourceConnectionFactory.getDbConnection();
        String sql = "insert into car_warn(id,vehicleno,vehiclecolor,warnsrc,warntype,warntime,infoid,infocontent,cjsj)values(?,?,?,?,?,?,?,?,?)";
        int insert = 0;
        try {
            System.out.println("报警数据插入。。。。");
            insert = runner.update(conn, sql, UUID.randomUUID().toString().replace("-", ""), packet.getVehicleNo().replaceAll("\u0000", ""), packet.getVehicleColor(), packet.getWarnSrc(), packet.getWarnTime(), packet.getInfoId(), packet.getInfoContent(), LocalDateTime.now());
        } catch (SQLException e) {
            log.info("数据存储错误：{}", e.getMessage());
        } finally {
            DbUtils.closeQuietly(conn);
        }
        return insert;
    }
    public static void batchAdd0x1402(List<JT809Packet0x1402> list){
//        QueryRunner runner = new QueryRunner();
        Connection conn = DataSourceConnectionFactory.getDbConnection();
        PreparedStatement preparedStatement=null;
        String sql = "insert into car_warn(id,vehicleno,vehiclecolor,warnsrc,warntype,warntime,infoid,infocontent,cjsj)values(?,?,?,?,?,?,?,?,?)";
        int insert = 0;
        try {
            System.out.println("报警数据批量插入。。。。");
            conn.setAutoCommit(false);
            preparedStatement=conn.prepareStatement(sql);
            for(JT809Packet0x1402 packet:list){
                preparedStatement.setString(1,UUID.randomUUID().toString().replace("-", ""));
                preparedStatement.setString(2,packet.getVehicleNo().replaceAll("\u0000", ""));
                preparedStatement.setInt(3,packet.getVehicleColor());
                preparedStatement.setInt(4,packet.getWarnSrc());
                ZoneId zoneId = ZoneId.systemDefault();
                ZonedDateTime zdt = packet.getWarnTime().atZone(zoneId);
                preparedStatement.setTimestamp(5,new Timestamp(java.util.Date.from(zdt.toInstant()).getTime()));
                preparedStatement.setInt(6,packet.getInfoId());
                preparedStatement.setString(7,packet.getInfoContent());
                preparedStatement.setTimestamp(8,new Timestamp(new java.util.Date().getTime()));
                preparedStatement.addBatch();
            }
            preparedStatement.executeBatch();
            conn.commit();
//            insert = runner.update(conn, sql, UUID.randomUUID().toString().replace("-", ""), packet.getVehicleNo().replaceAll("\u0000", ""), packet.getVehicleColor(), packet.getWarnSrc(), packet.getWarnTime(), packet.getInfoId(), packet.getInfoContent(), LocalDateTime.now());
        } catch (Exception e) {
            log.info("数据存储错误：{}", e.getMessage());
            try {
                if(conn!=null&&!conn.isClosed()){
                    conn.rollback();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            DbUtils.closeQuietly(conn);
        }
    }
    /**
     * 删除车辆定位信息，按照规定的时间格式删除
     */
    public static int del0x1202Data(String date) {
//        QueryRunner runner = new QueryRunner();
        Connection conn = DataSourceConnectionFactory.getDbConnection();
        Statement statement=null;
        //System.out.println("删除时间："+date);
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        String sql = "DELETE FROM car_gps WHERE to_char(cjsj,'yyyy-mm-dd hh24:mi:ss')<'" + date+"'";
        int del = 0;
        try {
            System.out.println("执行删除语句："+sql);
            statement=conn.createStatement();
            del = statement.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtils.closeQuietly(conn);
        }
        return del;
    }

    /**
     * 删除车辆报警信息，按照规定的时间格式删除
     */
    public static int del0x1402Data(String localDateTime) {
        QueryRunner runner = new QueryRunner();
        Connection conn = DataSourceConnectionFactory.getDbConnection();
        Statement statement=null;
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
       // System.out.println("删除时间："+localDateTime);
        String sql = "DELETE FROM car_warn WHERE to_char(cjsj,'yyyy-mm-dd hh24:mi:ss') < '" + localDateTime+"'";
        int del = 0;
        try {
            statement=conn.createStatement();
            del = statement.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtils.closeQuietly(conn);
        }
        return del;
    }
}
